How to create an Excel Content Pane App

I thought about calling this article “How I’m learning to love JavaScript”. But I decided if I did that it would be a lie. I don’t love it. Not yet at least. I’ll explain why in a bit.

Last we spoke of developing Excel task pane apps, I was dealing with the limitations of the Apps for Office development model. This time, I played it straight and didn’t try to exceed its limits. I thought life would be simple. I’d find a decent API to use for pulling in some data to Excel via a Content App. Simple right?

Simple… yes. But to a .NET loving Office developer-type like myself…

Simple != easy;

Like Excel task pane apps but different

Excel Content Panes are just like Task Pane apps. They use the same structure and utilize the same JavaScript API. The difference is Content Panes reside within an Excel spreadsheet. Thus, their purpose is to add content to file.

Excel Content Panes are in-line content and travel with the file. They are ideal for visualization apps like charts and graphs but you can do whatever you want with them. I decided I’d like to embed search results in a file. What we will do today is create a Content App that searches Bing and includes the search results in an Excel file.

Admin work – Content App project’s prerequisites

First, we have some pre-requisites to get out of the way. The Bing API is available from the Azure Data Market. It’s free but you need an account. You can take care of the pre-requisites now or you can return to them after building your Excel Content App.

I’m good with it either way. Here is what you need to do:

1. Create an Azure Data Market Account :: If you have a Microsoft Live ID, all you need to do is login. If you don’t have a Live ID, you have additional work.
2. Sign-up for the Bing API :: After you create your account/login, you need to sign-up for the BING Search API. I recommend starting with free option of 5,000 searches per month. You can always upgrade later.
3. Download the Bing API Quick Start & Code document :: This document contains code we want to steal. Namely, the Bing Proxy web application. This proxy app allows you to authenticate using your account key to create a token. You then use this token to search. It’s a pain but it keeps your account key safe from prying eyes that like to View Source. The app resides in the Creating a jQuery/AJAX web application. Just copy, paste, and FTP to a URL of your choosing.
4. Bing Search API Account Key: When logged into your Azure Data Market account, click My Account. Then click Account Keys. You can use the default key or create a new key. I recommend creating a new key. Don’t use the default one… ever.

Creating the JustBingIt! app

The JustBingIt! App is an Excel Content App. It is just a bit of HTML combined with JavaScript mixed with the insanity that is web development. If you are a web developer, please extend some grace to me. I’ve built websites in my time but I have always viewed HTML+Javascript+CSS as something I needed to know “just enough” for the task at hand. I’ve never bothered to master it and I regret it. But… there is no time like the present so hang with me here.

The way JustBingIt! works is…

1. The user selects any cell they want as long as the cell contains data.
2. The user clicks the Content Pane’s Use Selected Text button to enter the cell’s value into the search box.
3. The user selects the search type (web or image).
4. Or… they don’t do that and simply enter something else entirely. JustBingIt! doesn’t mind either way. She’s flexible and can make do with either method. She just wants text in the Search box. That is her only real demand.
5. The user clicks the Search button and search results display in the content pane.

It’s easy as 1-2-3-4-5. This is only slightly more difficult than 1-2-3 but not by much.

Creating the Content App for Excel project in Visual Studio 2012

We are more than 50% complete here. I’ve been talking a lot and I realize you might not think we have made any progress but these details are important. But now the action heats up.

Open Visual Studio 2012 and…

1. Click File > New Project to open the New Project dialog box.
2. Navigate to the Templates > Visual C# > Office/SharePoint/Apps node.
3. Select the App for Office 2013 project template.
4. Enter JustBingIt as the project Name.
5. Click OK.
6. In the Create App for Office dialog box, select Content app in Excel. Click Finish.

Visual Studio will create the project and open the JustBingIt.html and JustBingIt.js files. We want to deal with JustBingIt.html first. This file is the UI for the Excel content pane. It contains:

• the radio buttons for the search type selection
• the search field
• the buttons to grab the selected text and to call the search.

Listing 1 contains the HTML. I started to call it code but can’t make myself do that. It isn’t code… it’s markup.

Listing 1. JustBingIt.html

<!DOCTYPE html>
 
<html>
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
    <title>JustBingIt</title>
 
    <link rel="stylesheet" type="text/css" href="../Content/Office.css" />
    <link rel="stylesheet" type="text/css" href="../Content/App.css" />
 
    <script src="../Scripts/jquery-1.7.2.js"></script>
    <script src="../Scripts/Office/MicrosoftAjax.js"></script>
    <script src="../Scripts/Office/Office.js"></script>
 
    <!-- Add your JavaScript to the following file -->
    <script src="../Scripts/JustBingIt.js"></script>
  </head>
  <body>
<div id="wrapper">
   <h1>Just Bing It Already!</h1>
   <form id="my_form">
    <label for="service_op">Search Type</label>
    <input name="service_op" type="radio" value="Web" CHECKED /> Web
    <input name="service_op" type="radio" value="Image" /> Image
 
    <label for="query">Query</label>
       <br /><br />
    <input id="bt_useSelectedText" name="bt_useSelectedText"
		type="button" value="Use Selected Text" />
    <input id="query" name="query" type="text" size="60" /><br /><br />
    <input id="bt_search" name="bt_search" type="button" value="Search" />
   </form>
   <!-- Results will be placed into the following container. -->
   <div id="results">Content Apps rock the House!</div>
  </div>
  </body>
</html>

If you gave Listing 1 a thorough review, you might have noticed that I referenced Jquery 1.7.2. Office Apps utilize JQuery 1.6.2 by default. I suppose this is an additional pre-requisite. All you need to do is download it and put in the projects scripts folder.

Now for some code. Yes, I consider Javascript to be code. Copy & past the contents of Listing 2 into JustBingIt.js. And provide some explanations.

Listing 2. JustBingIt.js

// Add any initialization logic to this function.
Office.initialize = function (reason) {
 
    // Checks for the DOM to load.
    $(document).ready(function () {
        $("#bt_useSelectedText").click(function () { getData("query"); });
 
        // Attaches a click handler to the button.
        $('#bt_search').click(function (e) {
 
            // Clear the results div.
            $('#results').empty();
            var query = $('#query').val();
            var serviceOp = $('input[name=service_op]:checked', '#my_form').val();
            if (query)
                search(query, serviceOp);
        });
    })
} 
 
// Reads data from current selection.
function getData(elementIdToUpdate) {
    Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
    function (result) {
        if (result.status == "succeeded") {
            document.getElementById(elementIdToUpdate).value = result.value;
        }
    });
}
 
// Performs the search.
function search(query, serviceOp) {
    // Establish the data to pass to the proxy.
    var data = { q: query, sop: serviceOp, market: 'en-us' };
 
    // Calls the proxy, passing the query, service operation and market.
    /*$.getJSON(
		'https://officedeveloper.net/apps/BingSample/bing_proxy.php', 
		data, function (obj) {
 
		if (obj.d !== undefined) {
            var items = obj.d.results;
            for (var k = 0, len = items.length; k < len; k++) {
                var item = items[k];
                switch (item.__metadata.type) {
                    case 'WebResult':
                        showWebResult(item);
                        break;
                    case 'ImageResult':
                        showImageResult(item);
                        break;
                }
            }
        }
    });*/
 
    // XMLHttpRequest cannot load bing_proxy.php. Cross domain requests are not allowed.
    // So, we're using the GET request to load the proxy by passing "script" to dataType.
    request = $.ajax({
        url: 'https://officedeveloper.net/apps/BingSample/new/bing_proxy.php',
        type: "GET",
        dataType: "script",
        data: data
    }).done(requestDone);
}
 
function requestDone(result) {
    //When AJAX ready
    return false;
}
 
// This function is called in bing_proxy.php
function successSearchCallback(obj) {
    if (obj.d !== undefined) {
        var items = obj.d.results;
        for (var k = 0, len = items.length; k < len; k++) {
            var item = items[k];
            switch (item.__metadata.type) {
                case 'WebResult':
                    showWebResult(item);
                    break;
                case 'ImageResult':
                    showImageResult(item);
                    break;
            }
        }
    }
}
 
// Shows one item of Web result.
function showWebResult(item) {
    var p = document.createElement('p');
    var a = document.createElement('a');
    a.href = item.Url;
    $(a).append(item.Title);
    $(p).append(item.Description);
    // Append the anchor tag and paragraph with the description to the results div.
    $('#results').append(a, p);
}
 
// Shows one item of Image result.
function showImageResult(item) {
    var p = document.createElement('p');
    var a = document.createElement('a');
    a.href = item.MediaUrl;
    // Create an image element and set its source to the thumbnail.
    var i = document.createElement('img');
    i.src = item.Thumbnail.MediaUrl;
    // Make the object that the user clicks the thumbnail image.
    $(a).append(i);
    $(p).append(item.Title);
    // Append the anchor tag and paragraph with the title to the results div.
    $('#results').append(a, p);
}

Let's run through them one-at-a-time.

• Initialize event: This method attaches the getData method to the Use Selected Text button. It also attaches a function that checks the search type, clears the results <DIV> and calls the Search method.
• getData function: This function reads the value of the selected cell and inserts into the Search field.
• Search function: This function calls the Bing Proxy app you setup in the pre-requisites section. All you to do is change the URL to point your URL. Notice the request type is GET and the datatype is SCRIPT. This gets us around the Cross Domain issues that can be such a bugger in web development.
• successSearchCallback function: This is the AJAX callback function called by the Bing Search proxy upon a successful search. This method calls either showWebResult or ShowImageResult.
• showWebResult function: Shows the results of a web search in the Excel content pane.
• showImageResult function: Shows the results of an image search in the Excel content pane.

This is all there is to it. It's a simple concept but stands to have some powerful results.

Let's see what she can do!

You know what to do.

Hold your breath. Put your left-hand behind your back.
----AND----

Press F5… and find your content pane app in Excel 2013!

Simple, yet beautiful, no?

Special thanks to Victor for debugging my JavaScript and figuring out my cross domain issue. Victor, you are a JavaScript magician!

You may also be interested in:

• How to create Excel Task Pane Apps
• Excel 2013 development – 7 questions developers should ask
• Office UX guidelines for creating Excel 2013 content apps

Available Downloads:

JustBingIt! content pane app for Excel 2013